/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * InventoryManager.java
 *
 * Created on Mar 15, 2011, 9:48:56 PM
 */
package inventorymanagement;

import model.ColorTable;
import DataAccess.DBUtil;
import DataAccess.TableAccess;
import interact.iCheckForm;
import java.awt.Dimension;
import java.awt.Toolkit;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Ngoc Giang
 */
public class InventoryManager extends javax.swing.JFrame {

    Connection conn = DBUtil.getConnection(DBUtil.getInfoDB());

    /** Creates new form InventoryManager */
    public InventoryManager() {
        initComponents();
        Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
        this.setLocation(((int) screenSize.getWidth() - this.getWidth()) / 2,
                ((int) screenSize.getHeight() - this.getHeight()) / 2);
        getStatus();
        btnUpdate.setEnabled(false);
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jPanel3 = new javax.swing.JPanel();
        jLabel8 = new javax.swing.JLabel();
        btnUpdate = new javax.swing.JButton();
        txtUpdate = new javax.swing.JTextField();
        lblDetail = new javax.swing.JLabel();
        jScrollPane1 = new javax.swing.JScrollPane();
        tblDetail = new ColorTable();
        jPanel2 = new javax.swing.JPanel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        txtItemNotAva = new javax.swing.JTextField();
        txtItemAva = new javax.swing.JTextField();
        txtCategory = new javax.swing.JTextField();
        txtItem = new javax.swing.JTextField();
        btnItemNotAva = new javax.swing.JButton();
        btnSold = new javax.swing.JButton();
        btnCheck = new javax.swing.JButton();
        dchDate = new com.toedter.calendar.JDateChooser("MM/dd/yyyy", "##/##/####", '_');
        jLabel7 = new javax.swing.JLabel();
        btnBest = new javax.swing.JButton();
        mchMonth = new com.toedter.calendar.JMonthChooser();
        ychYear = new com.toedter.calendar.JYearChooser();
        jPanel1 = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        btnClose = new javax.swing.JButton();
        jLabel9 = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
        setResizable(false);

        jPanel3.setBorder(javax.swing.BorderFactory.createTitledBorder("Update quantity of items"));

        jLabel8.setText("Quantity Available:"); // NOI18N

        btnUpdate.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Images/edit.png"))); // NOI18N
        btnUpdate.setText("Update"); // NOI18N
        btnUpdate.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnUpdateActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel3Layout = new javax.swing.GroupLayout(jPanel3);
        jPanel3.setLayout(jPanel3Layout);
        jPanel3Layout.setHorizontalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel8)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(txtUpdate, javax.swing.GroupLayout.DEFAULT_SIZE, 126, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btnUpdate))
        );
        jPanel3Layout.setVerticalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(txtUpdate, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(btnUpdate)
                    .addComponent(jLabel8))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        lblDetail.setText("Table detail:"); // NOI18N

        tblDetail.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {

            }
        ));
        tblDetail.getTableHeader().setReorderingAllowed(false);
        jScrollPane1.setViewportView(tblDetail);

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder("Inventory's Status"));

        jLabel2.setText("Total Category:"); // NOI18N

        jLabel3.setText("Total Item:"); // NOI18N

        jLabel4.setText("Item not available:"); // NOI18N

        jLabel5.setText("Quantity of items:"); // NOI18N

        jLabel6.setText("Item sold in previous days:"); // NOI18N

        txtItemNotAva.setEditable(false);

        txtItemAva.setEditable(false);
        txtItemAva.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtItemAvaActionPerformed(evt);
            }
        });

        txtCategory.setEditable(false);
        txtCategory.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtCategoryActionPerformed(evt);
            }
        });

        txtItem.setEditable(false);
        txtItem.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtItemActionPerformed(evt);
            }
        });

        btnItemNotAva.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Images/report.png"))); // NOI18N
        btnItemNotAva.setText("Detail"); // NOI18N
        btnItemNotAva.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnItemNotAvaActionPerformed(evt);
            }
        });

        btnSold.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Images/report.png"))); // NOI18N
        btnSold.setText("Detail"); // NOI18N
        btnSold.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSoldActionPerformed(evt);
            }
        });

        btnCheck.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Images/ok.png"))); // NOI18N
        btnCheck.setText("Check Inventory"); // NOI18N
        btnCheck.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnCheckActionPerformed(evt);
            }
        });

        dchDate.setMaxSelectableDate(new java.util.Date(253370743308000L));
        dchDate.setMinSelectableDate(new java.util.Date(-62135791092000L));

        jLabel7.setText("Best-selling items in:");

        btnBest.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Images/report.png"))); // NOI18N
        btnBest.setText("Detail");
        btnBest.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnBestActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel2)
                    .addComponent(jLabel5)
                    .addComponent(jLabel4)
                    .addComponent(jLabel6)
                    .addComponent(jLabel7)
                    .addComponent(jLabel3))
                .addGap(25, 25, 25)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(txtItem, javax.swing.GroupLayout.DEFAULT_SIZE, 152, Short.MAX_VALUE)
                    .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                        .addComponent(dchDate, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addComponent(txtCategory, javax.swing.GroupLayout.DEFAULT_SIZE, 152, Short.MAX_VALUE)
                        .addComponent(txtItemAva)
                        .addComponent(txtItemNotAva)
                        .addGroup(jPanel2Layout.createSequentialGroup()
                            .addComponent(mchMonth, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(ychYear, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(btnCheck, javax.swing.GroupLayout.DEFAULT_SIZE, 142, Short.MAX_VALUE)
                    .addComponent(btnBest, javax.swing.GroupLayout.DEFAULT_SIZE, 142, Short.MAX_VALUE)
                    .addComponent(btnSold, javax.swing.GroupLayout.DEFAULT_SIZE, 142, Short.MAX_VALUE)
                    .addComponent(btnItemNotAva, javax.swing.GroupLayout.DEFAULT_SIZE, 142, Short.MAX_VALUE))
                .addContainerGap())
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(jPanel2Layout.createSequentialGroup()
                        .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel2)
                            .addComponent(txtCategory, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(6, 6, 6)
                        .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(txtItem, javax.swing.GroupLayout.DEFAULT_SIZE, 21, Short.MAX_VALUE)
                            .addComponent(jLabel3))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(txtItemAva, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jLabel5)))
                    .addComponent(btnCheck, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.PREFERRED_SIZE, 78, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(txtItemNotAva, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(btnItemNotAva)
                    .addComponent(jLabel4))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(btnSold)
                        .addComponent(jLabel6))
                    .addComponent(dchDate, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jLabel7)
                        .addComponent(btnBest))
                    .addComponent(mchMonth, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(ychYear, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(22, 22, 22))
        );

        jPanel1.setBackground(new java.awt.Color(0, 153, 153));

        jLabel1.setFont(new java.awt.Font("Times New Roman", 0, 36));
        jLabel1.setForeground(new java.awt.Color(255, 255, 255));
        jLabel1.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
        jLabel1.setText("Inventory Manager"); // NOI18N

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addGap(274, 274, 274)
                .addComponent(jLabel1, javax.swing.GroupLayout.DEFAULT_SIZE, 275, Short.MAX_VALUE)
                .addGap(314, 314, 314))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addGap(19, 19, 19)
                .addComponent(jLabel1)
                .addContainerGap(23, Short.MAX_VALUE))
        );

        btnClose.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Images/cancel.png"))); // NOI18N
        btnClose.setText("Close");
        btnClose.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnCloseActionPerformed(evt);
            }
        });

        jLabel9.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Images/supermarket.jpg"))); // NOI18N

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 843, Short.MAX_VALUE)
                    .addComponent(btnClose, javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jLabel9, 0, 0, Short.MAX_VALUE)
                            .addComponent(jPanel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))))
                .addContainerGap())
            .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(lblDetail)
                .addContainerGap(794, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jPanel2, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jLabel9, javax.swing.GroupLayout.PREFERRED_SIZE, 147, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jPanel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(0, 0, Short.MAX_VALUE)))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(lblDetail)
                .addGap(13, 13, 13)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 278, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(btnClose)
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents
    //method to get the status of inventory

    private void getStatus() {
        int countCate = 0;
        int countItem = 0;
        int countItemAva = 0;
        int countItemNotAva = 0;
        try {
            Statement st = conn.createStatement();
            String sql = "select count(*) as c from Categories";
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                countCate = rs.getInt(1);
            }
            txtCategory.setText(String.valueOf(countCate));

            String sql1 = "select count(*) as c from Items";
            rs = st.executeQuery(sql1);
            while (rs.next()) {
                countItem = rs.getInt(1);
            }
            txtItem.setText(String.valueOf(countItem));

            String sql2 = "select sum(QuantityAvailable) as Total from Items";
            rs = st.executeQuery(sql2);
            while (rs.next()) {
                countItemAva = rs.getInt(1);
            }
            txtItemAva.setText(String.valueOf(countItemAva));

            String sql3 = "select count(QuantityAvailable) as Total from Items where QuantityAvailable<1";
            rs = st.executeQuery(sql3);
            while (rs.next()) {
                countItemNotAva = rs.getInt(1);
            }
            txtItemNotAva.setText(String.valueOf(countItemNotAva));
        } catch (SQLException ex) {
            Logger.getLogger(InventoryManager.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    private void txtItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtItemActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_txtItemActionPerformed

    private void txtItemAvaActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtItemAvaActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_txtItemAvaActionPerformed

    private void btnCheckActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnCheckActionPerformed
        // TODO add your handling code here:
        try {
            lblDetail.setText("Inventory (Click on a row to select, enter the number of quantity to Quantity Available text field. Then click Update button to update)");
            btnUpdate.setEnabled(true);
            String sql = "select ItemID, ItemName, CategoryName, QuantityAvailable, Unit,"
                    + " Price, Descript, Manufacture, Status from Items"
                    + " INNER JOIN Categories"
                    + " ON Items.CategoryID = Categories.CategoryID";
            TableAccess.readToTable(sql, tblDetail);
        } catch (Exception ex) {
        }

    }//GEN-LAST:event_btnCheckActionPerformed

    private void txtCategoryActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtCategoryActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_txtCategoryActionPerformed

    private void btnItemNotAvaActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnItemNotAvaActionPerformed
        try {
            // TODO add your handling code here:
            lblDetail.setText("Item not Available (Click on a row to select, enter the number of quantity to Quantity Available text field. Then click Update button to update)");
            btnUpdate.setEnabled(true);
            String sql = "select ItemID, ItemName, CategoryName, QuantityAvailable, Unit,"
                    + " Price, Descript, Manufacture, Status from Items"
                    + " INNER JOIN Categories"
                    + " ON Items.CategoryID = Categories.CategoryID where QuantityAvailable<1";
            TableAccess.readToTable(sql, tblDetail);
        } catch (Exception ex) {
        }
    }//GEN-LAST:event_btnItemNotAvaActionPerformed

    private void btnSoldActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSoldActionPerformed
        // TODO add your handling code here:
        try {
            // TODO add your handling code here:  
            String date = "";
            btnUpdate.setEnabled(false);
            Date dt = dchDate.getDate();
            final SimpleDateFormat SDF = new SimpleDateFormat("MM/dd/yyyy");
            try {
                date = SDF.format(dt).toString();
            } catch (NullPointerException e) {
                JOptionPane.showMessageDialog(this, "Date null");
                dchDate.requestFocus();
                return;
            }
            lblDetail.setText("Sold in " + date + ":");
            String sql = "SELECT  ItemName, Sum(Quantity-QuantityReceipt) AS Total,"
                    + " DATE FROM Items INNER JOIN"
                    + " BillSellDetails ON BillSellDetails.ItemID = Items.ItemID INNER JOIN"
                    + " Bills ON Bills.BillID = BillSellDetails.BillID WHERE"
                    + " Bills.DATE ='" + date + "' Group By ItemName,DATE";
            TableAccess.readToTable(sql, tblDetail);
        } catch (Exception ex) {
        }
    }//GEN-LAST:event_btnSoldActionPerformed

    private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnUpdateActionPerformed
        // TODO add your handling code here:
        if (iCheckForm.checkNumber((txtUpdate.getText()))) {
            JOptionPane.showMessageDialog(this, "Invalid fomat");
            return;
        }
        int row = tblDetail.getSelectedRow();
        if (row == -1) {
            JOptionPane.showMessageDialog(this, "Click on a row to select, enter the number of quantity to Quantity Available text field. Then click Update button to update");
            return;
        }
        if (txtUpdate.getText().equals("")) {
            JOptionPane.showMessageDialog(this, "Click on a row to select, enter the number of quantity to Quantity Available text field. Then click Update button to update");
            return;
        }
        try {
            String itemID = tblDetail.getValueAt(row, 0).toString();
            String sql = "update Items set QuantityAvailable=" + txtUpdate.getText() + " where ItemID='" + itemID + "'";
            Statement st;
            st = conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(InventoryManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        JOptionPane.showMessageDialog(this, "Update quantity successful");
        getStatus();
        btnCheckActionPerformed(evt);
    }//GEN-LAST:event_btnUpdateActionPerformed

    private void btnCloseActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnCloseActionPerformed
        // TODO add your handling code here:
        this.dispose();
    }//GEN-LAST:event_btnCloseActionPerformed

    private void btnBestActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnBestActionPerformed
        // TODO add your handling code here:
        try {
            // TODO add your handling code here:
            int i = mchMonth.getMonth();
            int j = ychYear.getYear();
            lblDetail.setText("Best-selling items in " + (i+1) + "/" + j + ":");
            btnUpdate.setEnabled(false);
            String sql = "SELECT TOP 3 ItemName, Sum(Quantity-QuantityReceipt) AS Total FROM Items INNER JOIN"
                    + " BillSellDetails ON BillSellDetails.ItemID = Items.ItemID INNER JOIN"
                    + " Bills ON Bills.BillID = BillSellDetails.BillID WHERE"
                    + " MONTH(Bills.DATE) ='" + (i+1) + "' AND YEAR(Bills.DATE)='" + j +"'"
                    + " GROUP BY ItemName ORDER BY Total DESC";
            TableAccess.readToTable(sql, tblDetail);
        } catch (Exception ex) {
        }
    }//GEN-LAST:event_btnBestActionPerformed
    public static void main(String[] args) {
        InventoryManager inv = new InventoryManager();
        inv.setVisible(true);
    }
    /**
     * @param args the command line arguments
     */
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnBest;
    private javax.swing.JButton btnCheck;
    private javax.swing.JButton btnClose;
    private javax.swing.JButton btnItemNotAva;
    private javax.swing.JButton btnSold;
    private javax.swing.JButton btnUpdate;
    private com.toedter.calendar.JDateChooser dchDate;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JLabel lblDetail;
    private com.toedter.calendar.JMonthChooser mchMonth;
    private javax.swing.JTable tblDetail;
    private javax.swing.JTextField txtCategory;
    private javax.swing.JTextField txtItem;
    private javax.swing.JTextField txtItemAva;
    private javax.swing.JTextField txtItemNotAva;
    private javax.swing.JTextField txtUpdate;
    private com.toedter.calendar.JYearChooser ychYear;
    // End of variables declaration//GEN-END:variables
}
